package com.test40_JDBC;

import com.test40_JDBC.util.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Statement接口
 */
public class Demo02_Statement {

    /**
     * 插入操作
     */
    @Test
    public void test1() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();    // 创建数据库的操作对象
        String sql = "INSERT INTO user(name, age, birthday, salary, note) VALUES " +
                "('小李老师', 18, '2008-08-13', 3000.0, 'www.yootk.com')";
        int count = stmt.executeUpdate(sql);    // 执行SQL，返回更新的数据行数
        System.out.println("【数据更新操作】影响的数据行数：" + count);
        // 如果直接关闭了数据库连接，那么整个数据库的所有Statement对象也就自动关闭了
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 修改操作
     */
    @Test
    public void test2() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();    // 创建数据库的操作对象
        String sql = "UPDATE user SET name='李兴华编程训练营', age=1 WHERE uid=6";
        int count = stmt.executeUpdate(sql);    // 执行SQL，返回更新的数据行数
        System.out.println("【数据更新操作】影响的数据行数：" + count);
        // 如果直接关闭了数据库连接，那么整个数据库的所有Statement对象也就自动关闭了
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 删除操作
     */
    @Test
    public void test3() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();    // 创建数据库的操作对象
        String sql = "DELETE FROM user WHERE name LIKE '%李兴华%'";
        int count = stmt.executeUpdate(sql);    // 执行SQL，返回更新的数据行数
        System.out.println("【数据更新操作】影响的数据行数：" + count);
        // 如果直接关闭了数据库连接，那么整个数据库的所有Statement对象也就自动关闭了
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 查询操作
     */
    @Test
    public void test4() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "SELECT uid,name,age,birthday,salary,note from user";
        ResultSet rs = stmt.executeQuery(sql);  // 执行查询
        while (rs.next()) {     // 判断是否有数据
            long uid = rs.getLong("uid");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            Date birthday = rs.getDate("birthday");
            double salary = rs.getDouble("salary");
            String note = rs.getString("note");
            System.out.printf("【查询结果】编号：%s、姓名：%s、年龄：%s、生日：%s、工资：%s、介绍：%s\n",
                    uid, name, age, birthday, salary, note);
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 通过列索引号实现结果集获取
     */
    @Test
    public void test5() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "SELECT uid,name,age,birthday,salary,note from user";
        ResultSet rs = stmt.executeQuery(sql);  // 执行查询
        while (rs.next()) {     // 判断是否有数据
            long uid = rs.getLong(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            Date birthday = rs.getDate(4);
            double salary = rs.getDouble(5);
            String note = rs.getString(6);
            System.out.printf("【查询结果】编号：%s、姓名：%s、年龄：%s、生日：%s、工资：%s、介绍：%s\n",
                    uid, name, age, birthday, salary, note);
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 通过getObject实现接收
     */
    @Test
    public void test6() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "SELECT uid,name,age,birthday,salary,note from user";
        ResultSet rs = stmt.executeQuery(sql);  // 执行查询
        while (rs.next()) {     // 判断是否有数据
            Object uid = rs.getObject(1);
            Object name = rs.getObject(2);
            Object age = rs.getObject(3);
            Object birthday = rs.getObject(4);
            Object salary = rs.getObject(5);
            Object note = rs.getObject(6);
            System.out.printf("【查询结果】编号：%s、姓名：%s、年龄：%s、生日：%s、工资：%s、介绍：%s\n",
                    uid, name, age, birthday, salary, note);
        }
        JDBCUtil.closeConnection(conn);
    }

    @Test
    public void test7() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        Statement statement = conn.createStatement();
        String insertSQL = "INSERT INTO user(name, age, birthday, salary, note) VALUES " +
                "('李兴华', 18, '2008-08-13', 8000.0, 'www.yootk.com')";
        int count = statement.executeUpdate(insertSQL);     // 更新SQL操作
        statement.close();
        if (count > 0) {
            statement = conn.createStatement();
            String selectSQL = "SELECT LAST_INSERT_ID()";
            ResultSet rs = statement.executeQuery(selectSQL);   // 查询SQL操作
            if (rs.next()) {
                System.out.println("【数据更新成功】当前数据的ID内容为：" + rs.getLong(1));
            }
        }
        JDBCUtil.closeConnection(conn);
    }
}
